﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data.OracleClient;
using System.Data;
using VAdvantageSvc.DBase;
using System.Globalization;
using VAdvantage.Utility;
using VAdvantage.Model;
using VAdvantage.Controller;
using VAdvantage.Classes;
using VAdvantage.Logging;
using System.Web.Hosting;
using System.IO;
using System.Runtime.CompilerServices;



namespace VAdvantageSvc
{
    [ServiceContract(Namespace = "VAdvantageSvc")]
    public interface IDataServicesDB
    {
        [OperationContract]
        String GetDataXML(string sql, SqlParameter[] paramIn, string trxName, out CustomException ServiceError);

        [OperationContract]
        String SetDataXML(string xml, SqlParameter[] paramIn, string trxName, out CustomException ServiceError);

        [OperationContract]
        String SetDataLobXML(string xml, SqlParameter[] paramIn, string trxName, out CustomException ServiceError);

        [OperationContract]
        String GetScalar(string xml, SqlParameter[] paramIn, string trxName, out CustomException ServiceError);

        [OperationContract]
        Object[] GetScalarObjectArray(String[] sqlArr, string trxName, out CustomException ServiceError);


        //[OperationContract]
        //List<String> ExecuteMultipleQueries(List<String> sqlList, out CustomException ServiceError);

        [OperationContract]
        String GetDataXMLPaging(string sql, int start, int pageSize, out CustomException ServiceError);

        [OperationContract]
        bool Commit(string trxName);

        [OperationContract]
        bool Rollback(string trxName);
        [OperationContract]
        bool CloseTrx(String trxName);

        /// <summary>
        /// Executes KPI on Server side
        /// </summary>
        /// <param name="ctxDic"></param>
        /// <param name="KPI_Pane_ID"></param>
        [OperationContract]
        string ExecuteKPI(IDictionary<string, string> ctxDic, int KPI_Pane_ID, out CustomException ServiceError);

        [OperationContract]
        bool UpdateCustomerUser(string appUrl, int AD_User_ID, string Name, int AD_Client_ID, int count, bool isLoginUser, bool isDelete);
        
    }


    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, ConcurrencyMode = ConcurrencyMode.Multiple)]
    public class DataServicesDB : IDataServicesDB
    {

        #region "XmlString"

        public string SetDataLobXML(string sql, SqlParameter[] paramIn, string trxName, out CustomException ServiceError)
        {
            ServiceError = null;
            //OracleConnection Connection = new OracleConnection(connectionString);
            int i = -1;
            try
            {
                //  OracleCommand cmd = new OracleCommand(sql, Connection);
                i = DB.ExecuteQuery(sql, paramIn, trxName);
            }
            catch (Exception e)
            {
                ServiceError = new CustomException(e);
            }
            return i.ToString();
        }

        public string SetDataXML(string sql, SqlParameter[] paramIn, string trxName, out CustomException ServiceError)
        {
            ServiceError = null;
            int i = -1;
            try
            {
                i = DB.ExecuteQuery(sql, paramIn, trxName);
            }
            catch (Exception e)
            {
                ServiceError = new CustomException(e);
            }
            return i.ToString();
        }

        public string GetDataXML(string sql, SqlParameter[] paramIn, String trxName, out CustomException error)
        {
           
            DataSet ds = null;
            try
            {
                //sql = sql.Replace("SelectProduct", "'N' as SelectProduct");
                ds = DB.ExecuteDataset(sql, paramIn, trxName);
                //adapter.Fill(ds);//, 'Data');// (PageNumber - 1) * PageSize, PageSize, 'Data');
                //adapter.FillSchema(ds);//, SchemaType.Mapped, 'DataSchema');
                //if (PageSize > 0) // Reset Rowcount back to 0
                //{
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT 0 SET NO_BROWSETABLE OFF', (OracleCommand)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables[0];
                    //DataTable schema = ds.Tables['DataSchema'];
                    // data.Merge(schema);
                    // ds.Tables.Remove(schema);
                }

                ds = Util.SetUtcDateTime(ds);

                StringWriter sw = new StringWriter();

                ds.WriteXml(sw, XmlWriteMode.WriteSchema);

                //string schema = ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>");
                error = null;
               // return schema + ds.GetXml() + " </TestDataSet>";
                return sw.ToString();

            }
            catch (Exception err)
            {
                VLogger.Get().Severe(err.Message);

                error = new CustomException(err);
            }
            return "";
        }

        [MethodImpl(MethodImplOptions.Synchronized)]
        public String GetDataXMLPaging(string sql, int start, int pageSize, out CustomException ServiceError)
        {
            //string clientName = "Log";
            //string storedPath = Path.Combine(HostingEnvironment.ApplicationPhysicalPath, "");
            //storedPath += clientName;
            //VLogMgt.Initialize(true, storedPath);
            DataSet ds = null; ;
            OracleConnection Connection = new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["oracleConnectionString"]);
            ServiceError = null;
            try
            {
                // Ctx ctx = new Ctx(ctxDic);

                OracleDataAdapter adapter = new OracleDataAdapter();
                //VLogger.Get().Info(sql);
                adapter.SelectCommand = new OracleCommand(sql);
                Connection.Open();
                //VLogger.Get().Info(Connection.ConnectionString);
                adapter.SelectCommand.Connection = Connection;
                //if (PageSize > 0)// Set rowcount =PageNumber * PageSize for best performance
                //{
                //    long RowCount = PageNumber * PageSize;
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT ' + RowCount.ToString() + ' SET NO_BROWSETABLE ON', (OracleConnection)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                ds = new DataSet();
                adapter.Fill(ds, start, pageSize, "Table");
                //adapter.FillSchema(ds);//, SchemaType.Mapped, 'DataSchema');
                //if (PageSize > 0) // Reset Rowcount back to 0
                //{
                //    OracleCommand cmd = new OracleCommand('SET ROWCOUNT 0 SET NO_BROWSETABLE OFF', (OracleCommand)Connection);
                //    cmd.ExecuteNonQuery();
                //}
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables[0];
                    //DataTable schema = ds.Tables['DataSchema'];
                    // data.Merge(schema);
                    // ds.Tables.Remove(schema);
                }
            }
            catch (Exception err)
            {
                ServiceError = new CustomException(err);
               // VLogger.Get().Warning("Error Found in try block of GetDataXmlPagging");
                return "";
            }
            finally
            {
                Connection.Close();
            }

            ds = Util.SetUtcDateTime(ds);

            //string schema = ds.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>");
            // error = null;
            //VLogger.Get().Info("All Set..Now Returning Data");
           // return schema + ds.GetXml() + " </TestDataSet>";

            StringWriter sw = new StringWriter();
            ds.WriteXml(sw, XmlWriteMode.WriteSchema);
            return sw.ToString();
        }

        //public List<string> ExecuteMultipleQueries(List<string> sqlList, out CustomException error)
        //{
        //    OracleConnection Connection = new OracleConnection(DB.connectionString);

        //    List<String> logs = new List<string>();
        //    error = null;
        //    try
        //    {
        //        Connection.Open();

        //        foreach (String sql in sqlList)
        //        {
        //            try
        //            {
        //                OracleCommand cmd = new OracleCommand(sql, Connection);
        //                cmd.CommandType = CommandType.Text;
        //                cmd.ExecuteNonQuery();
        //            }
        //            catch
        //            {
        //                logs.Add(sql);
        //            }
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        error = new CustomException(e);
        //    }
        //    finally
        //    {
        //        Connection.Close();
        //    }
        //    return logs;
        //}

        #endregion

        public string GetScalar(string sql, SqlParameter[] paramIn, string trxName, out CustomException ServiceError)
        {
            ServiceError = null;
            object i = null;
            DataSet ds = null;
            try
            {
                ds = DB.ExecuteDataset(sql, paramIn, trxName);
                //i = DB.ExecuteScalar(sql, paramIn, trxName);
            }
            catch (Exception e)
            {
                ServiceError = new CustomException(e);
            }
         //   return (i == null || i == DBNull.Value) ? null : i.ToString();
            StringWriter wr = new StringWriter();
            if (ds.Tables.Count <1) 
            {
               //ServiceError = new CustomException(new Exception("Could Not Find Data"));
                return null;
            }
             ds = Util.SetUtcDateTime(ds);
            ds.WriteXml(wr, XmlWriteMode.WriteSchema);
            return wr.ToString();
        }


        public bool Commit(string trxName)
        {
            VAdvantage.DataBase.Trx trx = VAdvantage.DataBase.Trx.Get(trxName);
            if (trx != null)
                return trx.Commit();
            return true;
        }

        public bool Rollback(string trxName)
        {
            VAdvantage.DataBase.Trx trx = VAdvantage.DataBase.Trx.Get(trxName);
            if (trx != null)
                return trx.Rollback();
            return true;
        }

        public bool CloseTrx(string trxName)
        {
            VAdvantage.DataBase.Trx trx = VAdvantage.DataBase.Trx.Get(trxName);
            if (trx != null)
                trx.Close();
            return true;
        }


        #region KPI Related functions


        public string ExecuteKPI(IDictionary<string, string> ctxDic, int KPI_Pane_ID, out CustomException error)
        {
            DataTable dt = new DataTable();
            DataSet dsData = new DataSet();
            error = null;
            try
            {

                Ctx ctx = new Ctx(ctxDic);
                int userid = ctx.GetAD_User_ID();
                int roleid = ctx.GetAD_Role_ID();

                string sql = @"Select distinct RC_KPI_ID, Name, RC_KPICenter_ID from (Select center.Name as Name, kpi.RC_KPI_ID as RC_KPI_ID, center.RC_KPICenter_ID as RC_KPICenter_ID FROM RC_KPI kpi 
                                INNER JOIN RC_KPICenter center ON kpi.RC_KPI_ID = center.RC_KPI_ID 
                                INNER JOIN RC_KPIAccess acc ON center.RC_KPI_ID = acc.RC_KPI_ID 
                                WHERE kpi.IsActive = 'Y' AND ( (acc.AD_User_ID = " + userid + @") 
                                OR ( acc.AD_Role_ID = " + roleid + @") ) 
                                AND center.RC_KPIPane_ID = " + KPI_Pane_ID + "  ORDER BY center.SeqNo " + ")";

                IDataReader dr = VAdvantage.DataBase.DB.ExecuteReader(sql);
                int iRCKPIID = 0;
                string kpiName = "";

                while (dr.Read())
                {
                    StringBuilder sQuery = new StringBuilder();
                    iRCKPIID = int.Parse(dr["RC_KPI_ID"].ToString());
                    kpiName = dr["Name"].ToString();

                    X_RC_KPI xkpi = new X_RC_KPI(ctx, iRCKPIID, null);
                    sQuery.Append("Select '").Append(kpiName)
                        .Append("' AS Label, coalesce(");

                    if (xkpi.IsSum())
                        sQuery.Append("SUM(");
                    else if (xkpi.IsCount())
                        sQuery.Append("COUNT(");
                    else if (xkpi.IsMinimum())
                        sQuery.Append("MIN(");
                    else if (xkpi.IsMaximum())
                        sQuery.Append("MAX(");


                    MColumn column = MColumn.Get(ctx, xkpi.GetAD_Column_ID());
                    sQuery.Append(column.GetColumnName())
                        .Append("), 0) AS Value ");

                    string tableName = MTable.Get(ctx, column.GetAD_Table_ID()).GetTableName();
                    sQuery.Append("From ")
                        .Append(tableName);

                    sQuery.Append(" WHERE IsActive = 'Y' AND ");

                    int tabid = xkpi.GetAD_Tab_ID();
                    /** Window No					*/
                    int _windowNo = 1;
                    MTab tab = MTab.Get(ctx, tabid);

                    int AD_Window_ID = tab.GetAD_Window_ID();
                    GridWindowVO wVO = AEnv.GetMWindowVO(ctx, _windowNo, AD_Window_ID, 0);
                    if (wVO == null)
                    {
                        //do something.
                    }
                    GridWindow _mWindow = new GridWindow(wVO);
                    GridTab _mTab = _mWindow.GetTabByID(tabid);
                    GridField[] findFields = _mTab.GetFields();

                    MUserQuery obj = new MUserQuery(ctx, xkpi.GetAD_UserQuery_ID(), null);
                    DataSet ds = obj.GetQueryLines();
                    Query _query = GetQueryAdvanced(ds, tableName, findFields, ctx);

                    sQuery.Append(_query.GetWhereClause(true));
                    string tabWhere = _mTab.GetWhereClause();
                    if (tabWhere.Trim().Length > 0)
                        sQuery.Append(" AND ").Append(tabWhere);
                    string finalSQL = MRole.GetDefault(ctx, false).AddAccessSQL(sQuery.ToString(), _mTab.GetTableName(), MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);

                    if (dsData.Tables.Count == 0)
                    {
                        DataColumn dc;
                        dc = new DataColumn("ID");
                        dt.Columns.Add(dc);

                        dc = new DataColumn("Label");
                        dt.Columns.Add(dc);

                        dc = new DataColumn("TotalValue");
                        dt.Columns.Add(dc);

                        dsData.Tables.Add(dt);
                        dsData.Tables[0].Rows.Clear();
                    }
                    finalSQL = Env.ParseContext(ctx, 0, finalSQL, false, true);

                    IDataReader d = VAdvantage.DataBase.DB.ExecuteReader(finalSQL);


                    while (d.Read())
                    {
                        DataRow datarow = dsData.Tables[0].NewRow();
                        datarow[0] = int.Parse(dr["RC_KPICenter_ID"].ToString());
                        datarow[1] = d[0].ToString();
                        datarow[2] = double.Parse(d[1].ToString());

                        dsData.Tables[0].Rows.Add(datarow);
                    }
                    d.Close();
                }
                dr.Close();
            }
            catch (Exception err)
            {
                error = new CustomException(err);
                return "";
            }

            //string schema = dsData.GetXmlSchema().Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>", "<TestDataSet>");
            error = null;
           // return schema + dsData.GetXml() + " </TestDataSet>";

            StringWriter sw = new StringWriter();
            dsData.WriteXml(sw, XmlWriteMode.WriteSchema);
            return sw.ToString();

        }

        private GridField GetTargetMField(GridField[] _findFields, string columnName)
        {
            // if no column name, then return null
            if (columnName == null)
                return null;
            // else find field for the given column
            for (int c = 0; c < _findFields.Length; c++)
            {
                GridField field = _findFields[c];
                if (columnName.Equals(field.GetColumnName()))
                    return field;
            }
            return null;
        }


        private Object ParseValue(GridField field, object pp)
        {
            if (pp == null)
                return null;
            int dt = field.GetDisplayType();
            String inStr = pp.ToString();
            if (inStr == null || inStr.Equals(Null.NULLString) || inStr == "")
                return null;
            try
            {
                //	Return Integer
                if (dt == DisplayType.Integer
                    || (DisplayType.IsID(dt) && field.GetColumnName().EndsWith("_ID")))
                {
                    int i = int.Parse(inStr);
                    return i;
                }
                //	Return BigDecimal
                else if (DisplayType.IsNumeric(dt))
                {
                    return Decimal.Parse(inStr);       //DisplayType.GetNumberFormat(dt).GetFormatedValue(inStr);
                }
                //	Return Timestamp
                else if (DisplayType.IsDate(dt))
                {
                    String time = "";
                    try
                    {
                        return DateTime.Parse(inStr);
                    }
                    catch (Exception e)
                    {
                        //log.Log(Level.WARNING, inStr + "(" + inStr.GetType().FullName + ")" + e);
                        time = DisplayType.GetDateFormat(dt).Format(inStr);
                    }
                    try
                    {
                        return DateTime.Parse(time);
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
            catch (Exception ex)
            {
                //log.Log(Level.WARNING, "Object=" + inStr, ex);
                String error = ex.Message;
                if (error == null || error.Length == 0)
                    error = ex.ToString();
                StringBuilder errMsg = new StringBuilder();
                errMsg.Append(field.GetColumnName()).Append(" = ").Append(inStr).Append(" - ").Append(error);
                return null;
            }

            return inStr;
        }	//	parseValue


        private Query GetQueryAdvanced(DataSet dsAdvanceData, String _tableName, GridField[] gridField, Ctx ctx)
        {
            Query query = new Query(_tableName);
            // check if dataset have any table
            if (dsAdvanceData.Tables.Count == 1)
            {
                // for every row in dataset
                for (int i = 0; i < dsAdvanceData.Tables[0].Rows.Count; i++)
                {
                    DataRow dtRowObj = dsAdvanceData.Tables[0].Rows[i];
                    //	Column
                    string infoName = dtRowObj["KEYNAME"].ToString();
                    string columnName = dtRowObj["KEYVALUE"].ToString();
                    GridField field = GetTargetMField(gridField, columnName);
                    string columnSQL = field.GetColumnSQL(); //
                    //	Operator
                    string optr = dtRowObj["OPERATORNAME"].ToString(); //dtRowObj["OPERATOR"].ToString()
                    //	Value

                    object value = dtRowObj["VALUE1VALUE"];

                    object parsedValue = null;
                    if (value != null && value.ToString().Trim().StartsWith("adddays") || value.ToString().Trim().StartsWith("@"))
                    {
                        ;
                    }
                    else
                    {
                        parsedValue = ParseValue(field, value);
                    }

                    //string infoDisplay = dtRowObj["VALUE1NAME"].ToString();
                    String infoDisplay = null;

                    if (value == null || value.ToString().Length < 1)
                    {
                        if (Query.BETWEEN.Equals(optr))
                            continue;	//	no null in between
                        parsedValue = Null.NULLString;
                        infoDisplay = "NULL";
                    }
                    else
                    {
                        infoDisplay = dtRowObj["VALUE1NAME"].ToString();
                    }

                    //	Value2
                    // if "BETWEEN" selected
                    if (Query.BETWEEN.Equals(optr))
                    {

                        object value2 = dtRowObj["VALUE2VALUE"].ToString();
                        if (value2 == null || value2.ToString().Trim().Length < 1)
                            continue;

                        Object parsedValue2 = ParseValue(field, value2);
                        string infoDisplay_to = dtRowObj["VALUE2NAME"].ToString();
                        if (parsedValue2 == null)
                            continue;
                        // else add restriction where clause to query with between operator
                        query.AddRangeRestriction(columnSQL, parsedValue, parsedValue2, infoName,
                            infoDisplay, infoDisplay_to);
                    }
                    else
                    {
                        if (parsedValue == null && value != null && (value.ToString().Trim().StartsWith("adddays") || value.ToString().Trim().StartsWith("@")))
                        {
                            string Where = columnName + optr + value;
                            Where = Env.ParseContext(ctx, 0, Where, false);
                            query.AddRestriction(Where);
                        }
                        else
                        {
                            query.AddRestriction(columnSQL, optr, parsedValue, infoName, infoDisplay);
                        }
                    }
                }
            }
            return query;
        }
        #endregion

        #region IDataServicesDB Members


        public object[] GetScalarObjectArray(String[] sqlArr, string trxName, out CustomException ServiceError)
        {
            ServiceError = null;
            object[] arr = new object[sqlArr.Length];
            object val = null;

            try
            {
                int i = 0;
                foreach (String str in sqlArr)
                {

                    val = DB.ExecuteScalar(str, null, trxName);
                    arr[i] = val;
                    i++;
                }
            }
            catch (Exception e)
            {
                ServiceError = new CustomException(e);
            }

            return arr;
        }

        #endregion


        public bool UpdateCustomerUser(string appUrl, int AD_User_ID, string Name, int AD_Client_ID, int count, bool isLoginUser, bool isDelete)
        {
            try
            {
                var client = VAdvantageSvc.Classes.ServerEndPoint.GetCloudClient();
                System.Net.ServicePointManager.Expect100Continue = false;
                string result = client.CreateCustomerUser(appUrl, AD_User_ID, Name, AD_Client_ID, count, isLoginUser, isDelete,
                       VAdvantageSvc.Classes.ServerEndPoint.GetAccesskey());
                VLogger.Get().Info("CustomerUser:= " + result);
                client.Close();
            }
            catch
            {
                return false;
            }
            return true;
        }

        


    }
}
